import re
from flask import Blueprint
from flask import request,jsonify,send_from_directory
import os
from myfile import MyFile
import json
#课程
course = Blueprint('course',__name__)
from db import Db
import redis
#导入工具类
from mytree import MyTree
#导入分页类
from page import Page


@course.route('/addcourse_price/',methods=['GET'])
def addcourse_price():
    num = int(request.args.get('num',10))
    db = Db()
    db.update("update `course` set price=price+%d where id=45"%num)
    return {'code':200}

#模糊提示匹配
@course.route('/keyword/',methods=['GET'])
def keyword():
    keyword = request.args.get('keyword')
    keyword = keyword.strip(" ")
    id = request.args.get('id')
    if keyword == '':
        return jsonify([])
    #以空格分隔符
    mydb = Db()  
    if keyword.find(" ") == -1:
        res = mydb.get_all("select `keyword` from `search` where `keyword` like '%%%s%%' and uid=%s"%(keyword,id)) 
    else:
        #至少两个关键字
        keyword = keyword.split(" ")
        sql =''
        for item in keyword:
            sql +=" keyword like '%%%s%%' or"%item 
        sql = sql.rstrip('or')
        sql1 = "select `keyword` from `search` where (" + sql + ") and uid=%s"%id
        res = mydb.get_all(sql1)
       
    return jsonify(res)


#删除视频
@course.route('/delvideo/',methods = ['DELETE'])
def delvideo():
        id = request.args.get('id')
        uid = request.args.get('uid')
        dindex = request.args.get('dindex')
        type1 = request.args.get('type',0)
        db = Db()
        res = db.get_one("select `video` from `course` where id = %s and uid = %s"%(id,uid))
        res = eval(res['video'])  
        #批量删除 根据名字删除
        if type1 =='1':
               dindex =eval(dindex)
               for i in dindex:
                   #删除后转换类型
                   #删除物理文件
                #    myfile = MyFile()
                #    myfile.remove(uid,i)  
                   #删除数据库列表中指定索引
                   res.remove(i)   
               res = str(res).replace("'",'"')
               db.update("update `course` set video='%s' where id=%s"%(res,id))
        else:
            filename = res[int(dindex)]
            #删除数据库列表中指定索引
            res.pop(int(dindex))
            #删除后转换类型
            res = str(res).replace("'",'"')
            db.update("update `course` set video='%s' where id=%s"%(res,id))
            #删除物理文件
            # myfile = MyFile()
            # myfile.remove(uid,filename)      
        return {'code':200,'msg':'删除成功'}

#修改课程视频
@course.route('/updatevideo/',methods=['POST'])
def updatevideo():
    f = request.files['file']
    uid = request.form.get('uid')
    courseid = request.form.get('cid')
    dindex = request.form.get('dindex')
    dindex = int(dindex)
    myfile = MyFile()
    db = Db()
    newname = myfile.rename(f.filename)
    f.save(os.path.join('./static/upload/'+str(uid),newname))
    res = db.get_one("select `video` from `course` where id=%s"%courseid)
    res = eval(res['video']) 
    res.insert(dindex,newname)
    # filename = res[dindex+1]
    # myfile = MyFile()
    # myfile.remove(uid,filename)
    res.pop(dindex+1)
    res = str(res).replace("'",'"')
    db.update("update course set video='%s' where id=%s"%(res,courseid))
    
    return {'code':200,'msg':'修改成功','filename':newname}


#删除课程
@course.route('/delcourse/',methods=['DELETE'])
def delcourse():
    courseid = request.args.get('courseid')
    mydb =Db()
    mydb.delete("delete from `course` where id in "+"("+courseid+")")
    return {'code':200,'msg':'删除成功'}
    # type1 = request.args.get('type',0)
    # print(type1,type(type1))
    # courseid = request.args.get('courseid')
    # uid = request.args.get('uid')
    # mydb =Db()
    # if type1 == '1':
    #     courseid =eval(courseid)
    #     if len(courseid) >=2:
    #         courseid1 = tuple(courseid)
    #         mydb.delete("delete from `course` where id in "+str(courseid1))
    #     else:
    #         mydb.delete("delete from `course` where id=%s "%(courseid[0]))
    # else:
    #     mydb.delete("delete from `course` where uid=%s and id=%s"%(uid,courseid))
    # return {'code':200,'msg':'删除成功'}



import re
#关键字高亮
def keyword_highlight(keyword,data):
    if keyword:
    #遍历数据
        for item in data:
            title = item['title']
            if type(keyword) == list:
                str1 =""
                for i in keyword:
                    str1+= i+"|"
                str1.rstrip("|")
                res = re.sub(str1,"<font style='color:red'>\g<0></font>",title,flags = re.M |re.I)
                item['title_highlight'] = res
            else:
                # MULTILINE多个  IGNORECASE忽略大小写
                res = re.sub('%s'%keyword,"<font style='color:red'>\g<0></font>",title,flags = re.M |re.I)
                item['title_highlight'] = res
        return data
    else:
        for i in data:
            i['title_highlight'] = i['title']
        return data




@course.route('/getcourses/',methods=['GET'])
def getcourses():
    #课程id
   fid = request.args.get('fid',0)
   #用户id
   uid = request.args.get('uid','')
#    page当前页 pagesize 每页显示几条数据
   page =  int(request.args.get('page',1))
   pagesize =  int(request.args.get('pagesize',2))
   #id 与价格
   way = request.args.get('way',None)
   #正序倒叙
   reverse = request.args.get('reverse',None)
   #模糊查询
   keyword = request.args.get('keyword')
   keyword = keyword.strip()
   mydb = Db()
   if uid:
            way=int(way)
            reverse=int(reverse)
            sql = "select count(1) as total from course  where uid=%s "%uid
            sql2 = ""
            if keyword.find(" ") == -1:
                if way==0 and reverse==0:
                    sql2 = " order by id asc"   
                if way==0 and reverse==1:
                    sql2 = " order by id desc"   
                if way==1 and reverse==0:
                    sql2 = " order by price asc"   
                if way==1 and reverse==1:
                    sql2 = " order by price desc" 
                sql3 =""
                if keyword:
                   sql += " and title like '%%%s%%'"%keyword
                   sql3 += " and title like '%%%s%%' "%keyword
                
            else:
                keyword = keyword.split(" ")
                sql3 = ''
                for item in keyword:
                    sql3 += " title like '%%%s%%' or" %item
                sql3 = sql3.rstrip('or')
                sql += " and (%s)"%sql3
                sql3 = "and (%s) "%sql3
            sql1 = " select a.id,a.title,a.price,a.desc,case a.state when 0 then '待审' when 1 then '审核通过' when 2 then '审核拒绝' end as state,b.name,b.id as cateid from course a left join cate b on a.cid = b.id where a.uid = %s %s " % (uid,sql3)
            res = mydb.get_one(sql)
            page = Page(res['total'],page,pagesize)
            page_num = page.page_num()
            res = page.get_data(sql1,sql2)
            keyword_highlight(keyword,res)
            return {'code':200,'page_num':page_num,'data':res}
   else:
       res = mydb.get_one(" select * from `course` where id = %s order by id desc " % fid)
       return jsonify(res)
  
       
      

#获取分类详情
@course.route('/getcate/',methods = ['GET'])
def  getcate():
    fid = request.args.get('fid')
    mydb = Db()
    cate = mydb.get_one("select * from `cate` where id = %s"%fid)
    return jsonify(cate)


@course.route('/catelist/',methods=['GET'])
def catelist():
    id = request.args.get('id')
    #获取数据
    mydb = Db()
    sql = 'select * from `cate`'
    if id:
       sql += "where id in (%s)" %id
    #查询
    mydata = mydb.get_all(sql)
    #实例化
    tree = MyTree()
    #调用层级结构
    data = tree.get_tree(mydata)
    return jsonify(data)

@course.route('/catelistall/',methods=['GET'])
def catelistall():
    mydb = Db()
    sql = 'select * from `cate`'
    data = mydb.get_all(sql)
    return jsonify(data)


@course.route('/testlist/',methods=['GET'])
def testlist():
    myfile = MyFile()
    data = myfile.get_dirs()
    return jsonify(data)

# 添加课程
@course.route('/addlesson/',methods=['POST'])
def addlesson():
    # 接收参数
    f = request.files['file']
    title = request.form.get('title')
    desc = request.form.get('desc')
    cid = request.form.get('cid')
    price = request.form.get('price')
    datetime = request.form.get('datetime')
    # 写文件
    myfile = MyFile()
    myfile.mkdir(cid)
    upload = "./static/upload/%s/"%cid
    f.save(os.path.join(upload,f.filename))
    # 存入数据库
    db = Db()
    res=db.get_one("select video from `course` where cid='%s'"%cid)
    if res:
        res = json.loads(res['video'])
        res.append(f.filename)
        sql=" update `course` set `video`='%s' where cid='%s'"%(json.dumps(res),cid)
        print(sql)
        db.update(sql)
        return {"code":200,"meg":"添加成功"}
    else:
        file_list = []
        file_list.append(f.filename)
        print(file_list)
        sql="insert into `course` (`title`,`desc`,`cid`,`video`,`price`,`datetime`) values('%s','%s','%s','%s','%s','%s')"%(title,desc,cid,json.dumps(file_list),price,datetime)
        print(sql)
        db.insert(sql)
        return {"code":200,"meg":"添加成功"}

@course.route('/addcourse/',methods=['POST'])
def addcourse():
    fid = request.form.get('fid')
    title = request.form.get('title')
    desc = request.form.get('desc')
    uid = request.form.get('uid')
    params = request.form.get('params',None)
    mydb = Db()
    try:
        if params:
            mydb.insert("insert into `course` (`cid`,`title`,`desc`,`uid`,`params`) values(%s,'%s','%s',%s,'%s')"%(fid,title,desc,uid,params))
        else:
            mydb.insert("insert into `course` (`cid`,`title`,`desc`,`uid`) values(%s,'%s','%s',%s)"%(fid,title,desc,uid))
    except Exception as e:
        return {'code':400,'msg':'你输入的标题重复'}
    else:
        course = mydb.get_one("select * from `course` where `title`= '%s'"%title)
        return {'code':200,'msg':'添加成功','course_id':course['id']}
        


@course.route('/getcourse/',methods=['GET'])
def getcourse():
    cid = request.args.get('cid')
    mydb =Db()
    data = mydb.get_all("select * from `course` where `cid`= %s"%cid)
    for i in data:
        if i['video'] is None:
            i['video'] = None
        else:
            i['video'] = json.loads(i['video'])
        i['price'] = i['price']
    return jsonify(data)

@course.route('/updatecourse/',methods=['PUT'])
def updatecourse():
    try:
        id = request.form.get('id')
        title = request.form.get('title')
        desc = request.form.get('desc')
        price = int(request.form.get('price'))*100
        mydb = Db()
        mydb.update("update `course` set `title`='%s',`desc`='%s',`price`='%s' where `id`=%s"%(title,desc,price,id))
    except Exception as e:
        return {'code':400,'msg':'修改失败'}
    else:
        return {'code':200,'msg':'修改成功'}

@course.route('/updatecourses/',methods=['PUT'])
def updatecourses():
    try:
        id = request.form.get('id')
        fid = request.form.get('fid')
        title = request.form.get('title')
        desc = request.form.get('desc')
        params = request.form.get('params')
        mydb = Db()
        mydb.update("update `course` set `title`='%s',`desc`='%s',`params`='%s',`cid`=%s where `id`=%s"%(title,desc,params,fid,id))
    except Exception as e:
        return {'code':400,'msg':'修改失败'}
    else:
        return {'code':200,'msg':'修改成功'}




















